

* pop density
clear
import delimited "$P_Data/Germany Demographics Data/NUTS3_pop_density.csv"
drop unit
replace value = subinstr(value,",","",.)
destring value, replace force
rename value pop_density
rename geo nuts3
rename time year
keep if year>=2016
gener ger = regexm(nuts3,"DE")
keep if ger==1
drop ger

save "$P_Data_Processed/demog.dta", replace
 
* total population 
clear
import delimited "$P_Data/Germany Demographics Data/NUTS3_total_pop.csv"
drop unit
replace value = subinstr(value,",","",.)
destring value, replace force
rename value tot_pop
rename geo nuts3
rename time year
keep if year>=2016
gener ger = regexm(nuts3,"DE")
keep if ger==1
drop ger
drop sex
drop age
drop if year==2019

merge 1:1 nuts3 year using "$P_Data_Processed/demog.dta"
drop _merge
save "$P_Data_Processed/demog.dta", replace


* median age
clear
import delimited "$P_Data/Germany Demographics Data/NUTS3_med_age.csv"
drop if unit=="PC"
drop unit
replace value = subinstr(value,",","",.)
destring value, replace force
rename value med_age
rename geo nuts3
rename time year
keep if year>=2016
gener ger = regexm(nuts3,"DE")
keep if ger==1
drop ger
keep if indic_de=="MEDAGEPOP"
drop if year==2019
drop indic_de

merge 1:1 nuts3 year using "$P_Data_Processed/demog.dta"
drop _merge
save "$P_Data_Processed/demog.dta", replace


* employment
clear
import delimited "/$P_Data/Germany Demographics Data/NUTS3_employed_pop_thousands.csv"
drop wstatus nace_r2
replace value = subinstr(value,",","",.)
destring value, replace force
rename value employed_pop
replace employed_pop = employed_pop*1000
drop unit
rename geo nuts3
rename time year
keep if year>=2016
gener ger = regexm(nuts3,"DE")
keep if ger==1
drop ger
drop if year==2019

merge 1:1 nuts3 year using "$P_Data_Processed/demog.dta"
drop _merge
save "$P_Data_Processed/demog.dta", replace

* gdp
clear
import delimited "$P_Data/Germany Demographics Data/NUTS3_gdp.csv"
drop unit
rename geo nuts3
rename time year
replace value = subinstr(value,",","",.)
destring value, replace force
rename value GDP_current
keep if year>=2016
gener ger = regexm(nuts3,"DE")
keep if ger==1
drop ger
drop if year==2019


merge 1:1 nuts3 year using "$P_Data_Processed/demog.dta"
drop _merge
save"$P_Data_Processed/demog.dta", replace

** missing GDP and employment 2018 figures for NUTS3 - replace them by projections using NUTS2 growth rates
gener length = strlen(nuts3)
drop if length<4
gener nuts2_indicator = (length==4)

sort nuts3 year
by nuts3: gener gdp_growth_rate_2018 = (GDP_current - GDP_current[_n-1])/(GDP_current[_n-1]) if nuts2_indicator==1 & year==2018
by nuts3: gener employed_pop_growth_rate_2018 = (employed_pop - employed_pop[_n-1])/employed_pop[_n-1] if nuts2_indicator==1 & year==2018

gener nuts2 = substr(nuts3,1,4)

sort nuts2 gdp_growth_rate_2018
by nuts2: replace gdp_growth_rate_2018 = gdp_growth_rate_2018[1]

sort nuts2 employed_pop_growth_rate_2018
by nuts2: replace employed_pop_growth_rate_2018 = employed_pop_growth_rate_2018[1]

sort nuts3 year
by nuts3: replace GDP_current = GDP_current[_n-1]*(1+gdp_growth_rate_2018) if _n==_N & GDP_current==.
by nuts3: replace employed_pop = employed_pop[_n-1]*(1+employed_pop_growth_rate_2018) if _n==_N & employed_pop==.

drop gdp_growth_rate_2018
drop employed_pop_growth_rate_2018
drop length
drop if nuts2_indicator==1
drop nuts2_indicator
drop nuts2

save "$P_Data_Processed/demog.dta", replace

